<?php
namespace app\index\controller;
use think\Controller;
use think\Db;


class Datadisplay extends Controller
{
    public function getPrimaryTimes(){
        /*$conn = mysqli_connect("152.136.128.96:3306","root","spark1012","sheji");
        if ($conn->connect_error) {
            die("连接失败: " . $conn->connect_error);
        }

        $conn->query("set names utf8");
        */
        //$i_arr = [26,37,31,30,45,47,49,32,39,28,46,40,44,50,52,42,27,43,133,134,34,41,36,35,53,33,51,38,48];
        //$i_arr = [1,2,3,4,5,9];
        $i_arr = array();
        for($i = 158; $i <= 217; $i++) {
            $i_arr[] = $i;
        }


        /*$user_ids = array();
        for($i = 26; $i < 55; $i++){
            $user_ids[] = $i;
        }

        $result = behavior_times_compute("create",$user_ids,$conn);

        var_dump($result);*/

        echo "行为数据次数统计：";

        echo "<table border='1'>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>创建次数</th>
                <th>删除次数</th>
                <th>编辑次数</th>
                <th>回收次数</th>
                <th>移动次数</th>
                <th>通过次数</th>
                <th>取消通过次数</th>
                <th>评论次数</th>
                <th>点赞次数</th>
                <th>点踩次数</th>
                <th style='color:red;'>合计次数(无权重)</th>
                
            </tr>";




        foreach($i_arr as $i) {
            $user_ids = array();
            $user_ids[] = $i;


            $row = Db::table('sheji.users')
                ->where(['id'=> $i])
                ->find();
            //查询表头信息
            /*$sql = "SELECT * FROM users WHERE id = $i";
            $result = $conn->query($sql);
            $row = $result->fetch_assoc();*/


            echo "<tr>";
            echo "<td>" . $row['id'] . "</td>";
            echo "<td>" . $row['usr_name'] . "</td>";


            //特殊的行为类型需要单独查询
            //1.添加有效便利贴
            $record_num_add = 0;
            /*$sql = "SELECT * FROM business_canvas WHERE author_id = $i";
            $result = $conn->query($sql);
            if($result->num_rows > 0) {
                while ($row = $result->fetch_assoc()) {

                    if(strlen($row['contents']) > 0){
                        $record_num ++;
                    }
                }
            }
            echo "<td>" . $record_num . "</td>";*/


            $result = Db::table('sheji.business_canvas')
                ->where(['author_id'=> $i])
                ->select();

            if(sizeof($result) > 0) {
                foreach ($result as $row){
                    if(strlen($row['contents']) > 0){
                        $record_num_add ++;
                    }
                }
            }
            echo "<td>" . $record_num_add . "</td>";

            //2.删除有效便利贴
            $record_num_del = 0;
            /*$sql = "SELECT * FROM user_history LEFT JOIN business_canvas ON user_history.record_id = business_canvas.id".
                " WHERE user_id = $i AND bh_type = 'del'";
            $result = $conn->query($sql);
            if($result->num_rows > 0) {
                while ($row = $result->fetch_assoc()) {

                    if(strlen($row['contents']) > 0){
                        $record_num ++;
                    }
                }
            }*/
            $result = Db::table('sheji.user_history')
                ->leftJoin('sheji.business_canvas','user_history.record_id = business_canvas.id')
                ->where(['user_id'=> $i,'bh_type'=>'del'])
                ->select();

            if(sizeof($result) > 0){
                foreach ($result as $row){
                    if(strlen($row['contents']) > 0){
                        $record_num_del ++;
                    }
                }
            }

            echo "<td>" . $record_num_del . "</td>";


            //3.edit数据的debug
            $record_num_edit = 0;
            /*$sql = "SELECT * FROM user_history WHERE user_id = $i AND bh_type = 'edit'";
            $result = $conn->query($sql);
            if($result->num_rows > 0) {
                while ($row = $result->fetch_assoc()) {

                    if($row['record_id'] != 0){
                        $record_num ++;
                    }
                }
            }*/
            $result = Db::table('sheji.user_history')
                ->where(['user_id'=> $i,'bh_type'=>'edit'])
                ->select();
            if(sizeof($result) > 0){
                foreach ($result as $row){
                    if($row['record_id'] != 0){
                        $record_num_edit ++;
                    }
                }
            }
            echo "<td>" . $record_num_edit . "</td>";


            $total_times = $record_num_add + $record_num_del + $record_num_edit;
            //不同的行为类型
            //$types = ['recycle','move','pass','cancel_pass','open_public','favor','dislike'];
            $types = ['recycle','move','pass','cancel_pass','comment','favor','dislike'];

            foreach ($types as $type) {
                $result = $this->behavior_times_compute($type, $user_ids);

                echo "<td>" . sizeof($result) . "</td>";

                $total_times += sizeof($result);
            }

            echo "<td>" . $total_times . "</td>";
            echo "</tr>";

            /*if ($result->num_rows > 0) {


                // 输出数据
                while ($row = $result->fetch_assoc()) {
                    echo "<tr>";
                    echo "<td>" . $row['user_id'] . "</td>";
                    echo "<td>" . $row['usr_name'] . "</td>";
                    echo "<td>" . $row['bh_time'] . "</td>";
                    echo "</tr>";

                }


            } else {
                echo "0 结果";
            }*/
        }

        echo "</table>";


        //echo mysqli_error($conn);
        //$conn->close();




    }


    public function getLastTimeSequence(){
        $types = ['open','create','del','recycle','move','edit','pass','cancel_pass','open_public','favor','dislike'];
        //$user_ids = [26,37,31,30,45,47,49,32,39,28,46,40,44,50,52,42,27,43,133,134,34,41,36,35,53,33,51,38,48];

        //$user_ids = [1,2,3,4,5,9];
        $user_ids = array();
        for($i = 158; $i <= 217; $i++) {
            $user_ids[] = $i;
        }


        echo "最近50条行为数据";
        echo "<br>行为类型包括：打开小组工具、创建/删除/回收/移动/编辑 便利贴、通过/取消通过 便利贴、打开/点赞/点踩 公开的工具";
        echo "<table border='1'>
            <tr>
                <th>用户名</th>
                <th>行为类型</th>
                <th>时间</th>
                <th>项目小组</th>      
                <th>便利贴作者</th>
                <th>便利贴内容</th>
                <th>工具类型</th>
                       
            </tr>";


//for($i = 26; $i < 55; $i++) {
        //$user_ids = array();
        //$user_ids[] = $i;

        //$types = substr($types,0,strlen($types) - 1);

        $result = Db::table('sheji.user_history uh')
            ->leftJoin('sheji.users u','uh.user_id = u.id')
            ->where('user_id','in', $user_ids)
            ->where('bh_type','in',$types)
            ->order('bh_time','desc')
            ->limit(0,50)
            ->select();

        if (sizeof($result) > 0) {
            // 输出数据
            foreach ($result as $row) {
                echo "<tr>";
                echo "<td>" . $row['usr_name'] . "</td>";
                echo "<td>" . $row['bh_type'] . "</td>";
                echo "<td>" . $row['bh_time'] . "</td>";


                if($row['bh_type'] == 'open' || $row['bh_type'] == 'close'
                    || $row['bh_type'] == 'open_public' || $row['bh_type'] == 'close_public'){


                    $tool_id = $row['tool_id'];
                    $row_tmp = Db::table('sheji.tools')
                        ->leftJoin('sheji.projects','tools.prj_id = projects.id')
                        ->where('tools.id',$tool_id)
                        ->find();

                    echo "<td>" . $row_tmp['prj_name'] . "</td>";
                    echo "<td>" . '无' . "</td>";
                    echo "<td>" . '无' . "</td>";
                    echo "<td>" . $row_tmp['tool_type'] . "</td>";

                }else{

                    $record_id = $row['record_id'];
                    //$sql = "SELECT * FROM business_canvas b LEFT JOIN users u ON b.author_id = u.id LEFT JOIN tools t ON b.tool_id = t.id LEFT JOIN projects p ON t.prj_id = p.id WHERE b.id = $record_id";
                    $row_tmp = Db::table('sheji.business_canvas b')
                        ->leftJoin('sheji.users u','b.author_id = u.id')
                        ->leftJoin('sheji.tools t','b.tool_id = t.id')
                        ->leftJoin('sheji.projects p','t.prj_id = p.id')
                        ->where('b.id',$record_id)
                        ->find();

                    echo "<td>" . $row_tmp['prj_name'] . "</td>";
                    echo "<td>" . $row_tmp['usr_name'] . "</td>";
                    echo "<td>" . $row_tmp['contents'] . "</td>";

                    echo "<td>" . $row_tmp['tool_type'] . "</td>";

                }


                echo "</tr>";
            }
        } else {
            echo "<br><br>0 结果";
        }



//}

        echo "</table>";

//////////////////////////////////

        echo "<br><br>最近20条评论：";
        echo "<table border='1'>
            <tr>
                <th>评论用户</th>
                <th>被评用户</th>
                <th>时间</th>
                <th>便利贴内容</th>
                <th>评论内容</th>
                
                          
            </tr>";

        $cmt_result = $this->get_comment_result($user_ids,20);

        if(sizeof($cmt_result) > 0){
            foreach($cmt_result as $row ){
                echo "<tr>";
                echo "<td>" . $row['usr_name'] . "</td>";

                echo "<td>" . $row['author_name'] . "</td>";
                echo "<td>" . $row['time'] . "</td>";
                echo "<td>" . $row['record_contents'] . "</td>";
                echo "<td>" . $row['contents'] . "</td>";


                echo "</tr>";
            }

        }

        echo "</table>";


    }


    protected function get_comment_result($user_ids,$cmt_length){
        //$user_ids = implode(',', $user_arr);
        //var_dump($user_ids);
        //$sql = "SELECT * FROM comments cmt LEFT JOIN business_canvas bc ON cmt.record_id = bc.id".
        //    " WHERE user_id IN ($user_ids)";
        //$result = $conn->query($sql);
        $result = Db::table('sheji.comments cmt')
            ->leftJoin('sheji.business_canvas bc','cmt.record_id = bc.id')
            ->leftJoin('sheji.users u','u.id=cmt.user_id')
            ->field('u.usr_name,bc.contents AS record_contents,bc.author_id,cmt.*')
            ->where('user_id','in', $user_ids)
            ->order('time','desc')
            ->limit(0,$cmt_length)
            ->select();


        for($idx=0;$idx<sizeof($result);$idx++){
            $author = Db::table('sheji.users')
                ->field('usr_name')
                ->where('id',$result[$idx]['author_id'])
                ->find();

            $result[$idx]['author_name'] = $author['usr_name'];
        }

        return $result;
    }


    protected function behavior_times_compute($type,$user_ids){

        //$user_ids = implode(',', $user_arr);
        //var_dump($user_ids);
        //$sql = "SELECT * FROM user_history LEFT JOIN users ON user_history.user_id = users.id".
        //" WHERE user_id IN ($user_ids) AND bh_type = '$type'";



        if($type == 'comment'){
            //$sql = "SELECT * FROM comments".
            //" WHERE user_id IN ($user_ids) ";

            $result = Db::table('sheji.comments')
                ->where('user_id','in', $user_ids)
                ->select();
        }else{
            $result = Db::table('sheji.user_history')
                ->leftJoin('sheji.users','user_history.user_id = users.id')
                ->where('user_id','in', $user_ids)
                ->where('bh_type',$type)
                ->select();
        }

    //$result = $conn->query($sql);


        return $result;
    }
}